<?php

class Database_Adapter_Mysql extends Database_Adapter
{

	/**
	 * Keys are UPPERCASE SQL datatypes or the constants
	 * Database::INT_TYPE, Database::BIGINT_TYPE, or Database::FLOAT_TYPE.
	 *
	 * Values are:
	 * 0 = 32-bit integer
	 * 1 = 64-bit integer
	 * 2 = float or decimal
	 *
	 * @var array Associative array of datatypes to values 0, 1, or 2.
	 */
	protected $_numericDataTypes = array(
		Database::INT_TYPE => Database::INT_TYPE,
		Database::BIGINT_TYPE => Database::BIGINT_TYPE,
		Database::FLOAT_TYPE => Database::FLOAT_TYPE,
		'INT' => Database::INT_TYPE,
		'INTEGER' => Database::INT_TYPE,
		'MEDIUMINT' => Database::INT_TYPE,
		'SMALLINT' => Database::INT_TYPE,
		'TINYINT' => Database::INT_TYPE,
		'BIGINT' => Database::BIGINT_TYPE,
		'SERIAL' => Database::BIGINT_TYPE,
		'DEC' => Database::FLOAT_TYPE,
		'DECIMAL' => Database::FLOAT_TYPE,
		'DOUBLE' => Database::FLOAT_TYPE,
		'DOUBLE PRECISION' => Database::FLOAT_TYPE,
		'FIXED' => Database::FLOAT_TYPE,
		'FLOAT' => Database::FLOAT_TYPE
	);

	/**
	 * Creates a PDO DSN for the adapter from $this->_config settings.
	 *
	 * @return string
	 */
	protected function _dsn($option)
	{
		// the DSN
		unset($option['username']);
		unset($option['password']);
		unset($option['options']);
		unset($option['charset']);
		unset($option['persistent']);
		unset($option['driver_options']);

		// use all remaining parts in the DSN
		foreach ($option as $key => $val)
		{
			$option[$key] = "$key=$val";
		}

		return 'mysql:' . implode(';', $option);
	}

	/**
	 * Creates a PDO object and connects master host
	 * @return void
	 * @throws Database_Exception
	 */
	protected function _connect()
	{
		// if we already have a PDO object, no need to re-connect.
		if ($this -> _connection)
		{
			return;
		}

		if (!empty($this -> _config['charset']))
		{
			$initCommand = "SET NAMES '" . $this -> _config['charset'] . "'";
			$this -> _config['driver_options'][1002] = $initCommand;
			// 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
		}

		$master = $this -> getConfig('master');

		// use multiple master
		if (!isset($master['hostname']))
		{
			$max = count($master) - 1;
			if ($len > 0)
			{
				$master = $master[mt_rand(0, $max)];
			}
			else
			{
				$master = $master[0];
			}
		}

		$dsn = $this -> _dsn($master);

		// create PDO connection

		if (PROFILLING)
		{
			$token = Profiler::start('sql', 'connect ' . $dsn);
		}

		// add the persistence flag if we find it in our config array
		if (isset($this -> _config['persistent']) && ($this -> _config['persistent'] == true))
		{
			$this -> _config['driver_options'][PDO::ATTR_PERSISTENT] = true;
		}

		try
		{
			$this -> _connection = new PDO($dsn, $master['username'], $master['password'], $this -> _config['driver_options']);

			if (PROFILLING)
			{
				Profiler::stop($token);
			}

			// always use exceptions.
			$this -> _connection -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}

	}

	function _connectSlave()
	{
		// if we already have a PDO object, no need to re-connect.
		if ($this -> _slaveConnection)
		{
			return;
		}

		// try if no replication set
		if ($this -> _replicated == FALSE)
		{
			$this -> _connect();
			$this -> _slaveConnection = $this -> _connection;
			return;
		}

		if (!empty($this -> _config['charset']))
		{
			$initCommand = "SET NAMES '" . $this -> _config['charset'] . "'";
			$this -> _config['driver_options'][1002] = $initCommand;
			// 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
		}

		$slave = $this -> getConfig('slaves');

		// use multiple master
		if (!isset($slave['hostname']))
		{
			$max = count($slave) - 1;
			if ($len > 0)
			{
				$slave = $slave[mt_rand(0, $max)];
			}
			else
			{
				$slave = $slave[0];
			}
		}

		$dsn = $this -> _dsn($slave);

		// create PDO connection

		if (PROFILLING)
		{
			$token = Profiler::start('sql', 'connect ' . $dsn);
		}

		// add the persistence flag if we find it in our config array
		if (isset($this -> _config['persistent']) && ($this -> _config['persistent'] == true))
		{
			$this -> _config['driver_options'][PDO::ATTR_PERSISTENT] = true;
		}

		try
		{
			$this -> _slaveConnection = new PDO($dsn, $slave['username'], $slave['password'], $this -> _config['driver_options']);

			if (PROFILLING)
			{
				Profiler::stop($token);
			}

			// always use exceptions.
			$this -> _connection -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
	}

	/**
	 * Test if a connection is active
	 *
	 * @return boolean
	 */
	public function isConnected()
	{
		return ((bool)($this -> _connection instanceof PDO));
	}

	/**
	 * Force the connection to close.
	 *
	 * @return void
	 */
	public function closeConnection()
	{
		$this -> _connection = NULL;
		$this -> _slaveConnection = NULL;
	}

	/**
	 * Prepares an SQL statement.
	 *
	 * @param string $sql The SQL statement with placeholders.
	 * @param array $bind An array of data to bind to the placeholders.
	 * @return PDOStatement
	 */
	public function prepare($sql)
	{
		$stmt = new Database_Statement_Pdo($this, $sql);
		$stmt -> setFetchMode($this -> _fetchMode);
		return $stmt;
	}

	/**
	 * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
	 *
	 * As a convention, on RDBMS brands that support sequences
	 * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
	 * from the arguments and returns the last id generated by that sequence.
	 * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
	 * returns the last value generated for such a column, and the table name
	 * argument is disregarded.
	 *
	 * On RDBMS brands that don't support sequences, $tableName and $primaryKey
	 * are ignored.
	 *
	 * @param string $tableName   OPTIONAL Name of table.
	 * @param string $primaryKey  OPTIONAL Name of primary key column.
	 * @return string
	 */
	public function lastInsertId($tableName = null, $primaryKey = null)
	{
		$this -> _connect();
		return $this -> _connection -> lastInsertId();
	}

	/**
	 * Special handling for PDO query().
	 * All bind parameter names must begin with ':'
	 *
	 * @param string|Database_Select $sql The SQL statement with placeholders.
	 * @param array $bind An array of data to bind to the placeholders.
	 * @return Database_Statement_Pdo
	 * @throws Database_Exception To re-throw PDOException.
	 */
	public function query($sql, $bind = array())
	{
		if (empty($bind) && $sql instanceof Database_Select)
		{
			$bind = $sql -> getBind();
		}

		if (is_array($bind))
		{
			foreach ($bind as $name => $value)
			{
				if (!is_int($name) && !preg_match('/^:/', $name))
				{
					$newName = ":$name";
					unset($bind[$name]);
					$bind[$newName] = $value;
				}
			}
		}

		try
		{
			return parent::query($sql, $bind);
		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
	}

	/**
	 * Executes an SQL statement and return the number of affected rows
	 *
	 * @param  mixed  $sql  The SQL statement with placeholders.
	 *                      May be a string or Database_Select.
	 * @return integer      Number of rows that were modified
	 *                      or deleted by the SQL statement
	 */
	public function exec($sql)
	{
		if ($sql instanceof Database_Select)
		{
			$sql = $sql -> assemble();
		}

		try
		{
			$affected = $this -> getConnection() -> exec($sql);

			if ($affected === false)
			{
				$errorInfo = $this -> getConnection() -> errorInfo();
				/**
				 * @see Database_Exception
				 */

				throw new Database_Exception($errorInfo[2]);
			}

			return $affected;
		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
	}

	/**
	 * Quote a raw string.
	 *
	 * @param string $value     Raw string
	 * @return string           Quoted string
	 */
	protected function _quote($value)
	{
		if (is_int($value) || is_float($value))
		{
			return $value;
		}
		$this -> _connect();
		return $this -> _connection -> quote($value);
	}

	/**
	 * Begin a transaction.
	 */
	protected function _beginTransaction()
	{
		$this -> _connect();
		$this -> _connection -> beginTransaction();
	}

	/**
	 * Commit a transaction.
	 */
	protected function _commit()
	{
		$this -> _connect();
		$this -> _connection -> commit();
	}

	/**
	 * Roll-back a transaction.
	 */
	protected function _rollBack()
	{
		$this -> _connect();
		$this -> _connection -> rollBack();
	}

	/**
	 * Set the PDO fetch mode.
	 *
	 * @todo Support FETCH_CLASS and FETCH_INTO.
	 *
	 * @param int $mode A PDO fetch mode.
	 * @return void
	 * @throws Database_Exception
	 */
	public function setFetchMode($mode)
	{
		//check for PDO extension
		if (!extension_loaded('pdo'))
		{
			/**
			 * @see Database_Exception
			 */
			throw new Database_Exception('The PDO extension is required for this adapter but the extension is not loaded');
		}
		switch ($mode)
		{
			case PDO::FETCH_LAZY :
			case PDO::FETCH_ASSOC :
			case PDO::FETCH_NUM :
			case PDO::FETCH_BOTH :
			case PDO::FETCH_NAMED :
			case PDO::FETCH_OBJ :
				$this -> _fetchMode = $mode;
				break;
			default :
				/**
				 * @see Database_Exception
				 */

				throw new Database_Exception("Invalid fetch mode '$mode' specified");
				break;
		}
	}

	/**
	 * Check if the adapter supports real SQL parameters.
	 *
	 * @param string $type 'positional' or 'named'
	 * @return bool
	 */
	public function supportsParameters($type)
	{
		switch ($type)
		{
			case 'positional' :
			case 'named' :
			default :
				return true;
		}
	}

	/**
	 * Retrieve server version in PHP style
	 *
	 * @return string
	 */
	public function getServerVersion()
	{
		$this -> _connect();
		try
		{
			$version = $this -> _connection -> getAttribute(PDO::ATTR_SERVER_VERSION);
		}
		catch (PDOException $e)
		{
			// In case of the driver doesn't support getting attributes
			return null;
		}
		$matches = null;
		if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches))
		{
			return $matches[1];
		}
		else
		{
			return null;
		}
	}

	/**
	 * @return string
	 */
	public function getQuoteIdentifierSymbol()
	{
		return "`";
	}

	/**
	 * Returns a list of the tables in the database.
	 *
	 * @return array
	 */
	public function listTables()
	{
		return $this -> fetchCol('SHOW TABLES');
	}

	/**
	 * Returns the column descriptions for a table.
	 *
	 * The return value is an associative array keyed by the column name,
	 * as returned by the RDBMS.
	 *
	 * The value of each array element is an associative array
	 * with the following keys:
	 *
	 * SCHEMA_NAME      => string; name of database or schema
	 * TABLE_NAME       => string;
	 * COLUMN_NAME      => string; column name
	 * COLUMN_POSITION  => number; ordinal position of column in table
	 * DATA_TYPE        => string; SQL datatype name of column
	 * DEFAULT          => string; default expression of column, null if none
	 * NULLABLE         => boolean; true if column can have nulls
	 * LENGTH           => number; length of CHAR/VARCHAR
	 * SCALE            => number; scale of NUMERIC/DECIMAL
	 * PRECISION        => number; precision of NUMERIC/DECIMAL
	 * UNSIGNED         => boolean; unsigned property of an integer type
	 * PRIMARY          => boolean; true if column is part of the primary key
	 * PRIMARY_POSITION => integer; position of column in primary key
	 * IDENTITY         => integer; true if column is auto-generated with unique
	 * values
	 *
	 * @param string $tableName
	 * @param string $schemaName OPTIONAL
	 * @return array
	 */
	public function describeTable($tableName, $schemaName = null)
	{
		// @todo  use INFORMATION_SCHEMA someday when MySQL's
		// implementation has reasonably good performance and
		// the version with this improvement is in wide use.

		if ($schemaName)
		{
			$sql = 'DESCRIBE ' . $this -> quoteIdentifier("$schemaName.$tableName", true);
		}
		else
		{
			$sql = 'DESCRIBE ' . $this -> quoteIdentifier($tableName, true);
		}
		$stmt = $this -> query($sql);

		// Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO
		// connection
		$result = $stmt -> fetchAll(Database::FETCH_NUM);

		$field = 0;
		$type = 1;
		$null = 2;
		$key = 3;
		$default = 4;
		$extra = 5;

		$desc = array();
		$i = 1;
		$p = 1;
		foreach ($result as $row)
		{
			list($length, $scale, $precision, $unsigned, $primary, $primaryPosition, $identity) = array(
				null,
				null,
				null,
				null,
				false,
				null,
				false
			);
			if (preg_match('/unsigned/', $row[$type]))
			{
				$unsigned = true;
			}
			if (preg_match('/^((?:var)?char)\((\d+)\)/', $row[$type], $matches))
			{
				$row[$type] = $matches[1];
				$length = $matches[2];
			}
			else
			if (preg_match('/^decimal\((\d+),(\d+)\)/', $row[$type], $matches))
			{
				$row[$type] = 'decimal';
				$precision = $matches[1];
				$scale = $matches[2];
			}
			else
			if (preg_match('/^float\((\d+),(\d+)\)/', $row[$type], $matches))
			{
				$row[$type] = 'float';
				$precision = $matches[1];
				$scale = $matches[2];
			}
			else
			if (preg_match('/^((?:big|medium|small|tiny)?int)\((\d+)\)/', $row[$type], $matches))
			{
				$row[$type] = $matches[1];
				// The optional argument of a MySQL int type is not precision
				// or length; it is only a hint for display width.
			}
			if (strtoupper($row[$key]) == 'PRI')
			{
				$primary = true;
				$primaryPosition = $p;
				if ($row[$extra] == 'auto_increment')
				{
					$identity = true;
				}
				else
				{
					$identity = false;
				}
				++$p;
			}
			$desc[$row[$field]] = array(
				'SCHEMA_NAME' => null, // @todo
				'TABLE_NAME' => $tableName,
				'COLUMN_NAME' => $row[$field],
				'COLUMN_POSITION' => $i,
				'DATA_TYPE' => $row[$type],
				'DEFAULT' => $row[$default],
				'NULLABLE' => (bool)($row[$null] == 'YES'),
				'LENGTH' => $length,
				'SCALE' => $scale,
				'PRECISION' => $precision,
				'UNSIGNED' => $unsigned,
				'PRIMARY' => $primary,
				'PRIMARY_POSITION' => $primaryPosition,
				'IDENTITY' => $identity
			);
			++$i;
		}
		return $desc;
	}

	/**
	 * Adds an adapter-specific LIMIT clause to the SELECT statement.
	 *
	 * @param  string $sql
	 * @param  integer $count
	 * @param  integer $offset OPTIONAL
	 * @throws Database_Exception
	 * @return string
	 */
	public function limit($sql, $count, $offset = 0)
	{
		$count = intval($count);
		if ($count <= 0)
		{
			/** @see Database_Exception */

			throw new Database_Exception("LIMIT argument count=$count is not valid");
		}

		$offset = intval($offset);
		if ($offset < 0)
		{
			/** @see Database_Exception */

			throw new Database_Exception("LIMIT argument offset=$offset is not valid");
		}

		$sql .= " LIMIT $count";
		if ($offset > 0)
		{
			$sql .= " OFFSET $offset";
		}

		return $sql;
	}

}
